﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using KCL.Code.Entities;

namespace KCL.Code.Data
{
	public class OrderDAO : BaseDAO
	{
		public List<Order> GetOrders(string fromDate, string sortExpression, int maximumRows, int startRowIndex, bool isFinished)
		{
			SqlConnection myConnection = this.GetConnection();
			string sqlStatement = string.Format("WITH OrderedOrder AS (SELECT *, ROW_NUMBER() OVER (ORDER BY {0}) AS 'RowNumber' FROM [Order] WHERE isFinished={1}) SELECT * FROM OrderedOrder WHERE RowNumber BETWEEN {2} AND {3}", sortExpression != string.Empty ? sortExpression : "id", isFinished ? 1 : 0, startRowIndex, startRowIndex + maximumRows);
			SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
			SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
			myConnection.Open();
			DataTable dtOder = new DataTable();
			myAdapter.Fill(dtOder);
			List<Order> results = new List<Order>();

			foreach (DataRow drOrder in dtOder.Rows)
			{
				Order order = new Order();
				order.id = Convert.ToInt32(drOrder["id"]);
				order.customerId = Convert.ToInt32(drOrder["customerId"]);
				myCommand.CommandText = string.Format("SELECT name FROM [Customer] WHERE [id]={0}", order.customerId);
				order.customerName = Convert.ToString(myCommand.ExecuteScalar());
				order.strTotalPrice = drOrder["strTotalPrice"].ToString();
				order.totalPrice = Convert.ToDouble(drOrder["totalPrice"]);
				order.discount = Convert.ToInt32(drOrder["discount"]);
				order.prePay = Convert.ToInt32(drOrder["prePay"]);
				order.remain = Convert.ToInt32(drOrder["remain"]);
				order.deliveryDate = drOrder["deliveryDate"] != DBNull.Value ? Convert.ToDateTime(drOrder["deliveryDate"]).ToString("dd/MM/yyyy") : "";
				order.createdDate = drOrder["createdDate"] != DBNull.Value ? Convert.ToDateTime(drOrder["createdDate"]).ToString("dd/MM/yyyy") : "";
				order.createdBy = Convert.ToInt32(drOrder["createdBy"]);
				order.payMethodId = Convert.ToInt32(drOrder["payMethodId"]);
				order.code = drOrder["code"].ToString();
				order.isFinished = Convert.ToBoolean(drOrder["isFinished"]);
				order.orderMaterials = new List<OrderMaterial>();
				myCommand.CommandText = string.Format("SELECT * FROM [OrderMaterial] WHERE [orderId]={0}", order.id);
				DataTable dtOderMaterial = new DataTable();
				myAdapter.Fill(dtOderMaterial);
				foreach (DataRow drOderMaterial in dtOderMaterial.Rows)
				{
					OrderMaterial orderMaterial = new OrderMaterial();
					orderMaterial.id = Convert.ToInt32(drOderMaterial["id"]);
					orderMaterial.materialId = drOderMaterial["materialId"].ToString();
					orderMaterial.description = drOderMaterial["description"].ToString();
					orderMaterial.orderQuantity = Convert.ToInt32(drOderMaterial["orderQuantity"]);
					orderMaterial.finishedQuantity = Convert.ToInt32(drOderMaterial["finishedQuantity"]);
					orderMaterial.finishedDate = drOderMaterial["finishedDate"].ToString();
					myCommand.CommandText = string.Format("SELECT * FROM [OrderMaterialPhase] WHERE [orderMaterialId]={0}", orderMaterial.id);
					DataTable dtOderMaterialPhase = new DataTable();
					myAdapter.Fill(dtOderMaterialPhase);
					orderMaterial.orderMaterialPhases = new List<OrderMaterialPhase>();
					foreach (DataRow dr in dtOderMaterialPhase.Rows)
						orderMaterial.orderMaterialPhases.Add(new OrderMaterialPhase(Convert.ToInt32(dr[0]), Convert.ToInt32(dr[1]), Convert.ToDouble(dr[2]), Convert.ToInt32(dr[3])));
					order.orderMaterials.Add(orderMaterial);
				}
				results.Add(order);
			}
			myConnection.Close();
			return results;
		}

		public int GetTotalOrder(string fromDate, bool isFinished)
		{
			SqlConnection myConnection = this.GetConnection();
			string sqlStatement = string.Format("SELECT COUNT(*) FROM [Order] WHERE isFinished={0}", isFinished ? 1 : 0);
			SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
			myConnection.Open();
			int totalOrder = Convert.ToInt32(myCommand.ExecuteScalar());
			myConnection.Close();
			return totalOrder;
		}

		public string CreateOrder(Order order)
		{
			SqlConnection myConnection = this.GetConnection();
			SqlTransaction myTransaction = null;
			string code = "";
			SqlCommand myCommand = new SqlCommand();
			try
			{
				myConnection.Open();
				myTransaction = myConnection.BeginTransaction();
				myCommand.Connection = myConnection;
				myCommand.Transaction = myTransaction;
				myCommand.CommandText = string.Format("INSERT INTO [Order]([customerId],[strTotalPrice],[totalPrice],[discount],[prePay],[remain],[deliveryDate],[createdBy],[code],[payMethodId],[packageId]) VALUES({0}, N'{1}', {2}, {3}, {4}, {5}, convert(date, '{6}', 103), {7}, N'xXx', {8}, {9})", order.customerId, order.strTotalPrice, order.totalPrice, order.discount, order.prePay, order.remain, order.deliveryDate, order.createdBy, order.payMethodId, order.packageId);
				myCommand.CommandText = myCommand.CommandText.Replace("N''", "NULL").Replace("'',", "NULL,");
				myCommand.ExecuteNonQuery();
				myCommand.CommandText = "SELECT @@IDENTITY";
				object orderId = myCommand.ExecuteScalar();
				code = "DH" + orderId.ToString();
				myCommand.CommandText = string.Format("UPDATE [Order] SET [code]=N'{0}' WHERE [id]={1}", code, orderId);
				myCommand.ExecuteNonQuery();

				foreach (OrderMaterial orderMaterial in order.orderMaterials)
				{
					myCommand.CommandText = string.Format("INSERT INTO [OrderMaterial]([orderId],[materialId],[description],[orderQuantity]) VALUES({0}, N'{1}', N'{2}', {3})", orderId, orderMaterial.materialId, orderMaterial.description, orderMaterial.orderQuantity);
					myCommand.ExecuteNonQuery();
					myCommand.CommandText = "SELECT @@IDENTITY";
					orderMaterial.id = Convert.ToInt32(myCommand.ExecuteScalar());
					foreach (OrderMaterialPhase orderMaterialPhase in orderMaterial.orderMaterialPhases)
					{
						myCommand.CommandText = string.Format("INSERT INTO [OrderMaterialPhase]([orderMaterialId],[phaseId],[weight],[quantity]) VALUES({0}, {1}, {2}, {3})", orderMaterial.id, orderMaterialPhase.phaseId, orderMaterialPhase.weight, orderMaterialPhase.quantity);
						myCommand.ExecuteNonQuery();
					}
				}
				if (order.prePay != null)
				{
					myCommand.CommandText = string.Format("INSERT INTO [OrderDebt] VALUES('{0}', {1}, {2}, GETDATE())", code, 1, order.prePay);
					myCommand.ExecuteNonQuery();
				}
				myTransaction.Commit();
				myConnection.Close();
			}
			catch (Exception ex)
			{
				myTransaction.Rollback();
				throw ex;
			}
			return code;
		}

		public int GetPayMethodId(int payMethodType, int payMethodAmount)
		{
			int payMethodId = 0;
			if (payMethodType == 1) payMethodId = 1;
			else
			{
				SqlConnection myConnection = this.GetConnection();
				string sqlStatement = string.Format("SELECT [id] FROM [PayMethod] WHERE [type]={0} AND [amount]={1}", payMethodType, payMethodAmount);
				SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
				myConnection.Open();
				Object payMethodIdObj = myCommand.ExecuteScalar();
				if (payMethodIdObj == null)
				{
					myCommand.CommandText = string.Format("INSERT INTO [PayMethod] VALUES(N'{0}', {1}, {2})", payMethodType == 2 ? "Sau " + payMethodAmount + " ngày" : "Thu tru?c khi giao " + payMethodAmount + "%", payMethodType, payMethodAmount);
					myCommand.ExecuteNonQuery();
					myCommand.CommandText = "SELECT @@IDENTITY";
					payMethodIdObj = myCommand.ExecuteScalar();
				}
				payMethodId = Convert.ToInt32(payMethodIdObj);
				myConnection.Close();
			}
			return payMethodId;
		}

		public bool UpdateOrder(string id, string name, string address, string contactAddress, string contactPerson, string phone, string mobile, string fax, string accountNumber, string bank, string taxNumber, string director)
		{
			SqlConnection myConnection = this.GetConnection();
			string sqlStatement = string.Format("UPDATE [Order] SET name=N'{0}', address=N'{1}', contactAddress=N'{2}', contactPerson=N'{3}', phone=N'{4}', mobile=N'{5}', fax=N'{6}', accountNumber=N'{7}', bank=N'{8}', taxNumber=N'{9}', director=N'{10}' WHERE id={11}", name, address, contactAddress, contactPerson, phone, mobile, fax, accountNumber, bank, taxNumber, director, id);
			sqlStatement = sqlStatement.Replace("N''", "NULL").Replace("'',", "NULL,");
			SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
			catch (Exception ex)
			{
				throw ex;
			}
			return true;
		}

        public bool UpdateRemain(string orderCode)
        {
            SqlConnection myConnection = this.GetConnection();
            string sqlStatement = string.Format(@"
                UPDATE [Order]
                SET remain = TotalPrice -
			                (SELECT ISNULL(SUM(payAmount), 0)
			                FROM [OrderDebt] 
			                WHERE [OrderDebt].orderCode = '{0}')
                WHERE code = '{0}'", orderCode);
            sqlStatement = sqlStatement.Replace("N''", "NULL").Replace("'',", "NULL,");
            SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
		public Order GetOrderDetail(int orderId)
		{
			SqlConnection myConnection = this.GetConnection();
			string sqlStatement = string.Format("SELECT * FROM [Order] WHERE id={0}", orderId);
			SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
			SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
			myConnection.Open();
			DataTable dtOder = new DataTable();
			myAdapter.Fill(dtOder);
			List<Order> results = new List<Order>();

			foreach (DataRow drOrder in dtOder.Rows)
			{
				Order order = new Order();
				order.id = Convert.ToInt32(drOrder["id"]);
				order.customerId = Convert.ToInt32(drOrder["customerId"]);
				myCommand.CommandText = string.Format("SELECT name FROM [Customer] WHERE [id]={0}", order.customerId);
				order.customerName = Convert.ToString(myCommand.ExecuteScalar());
				order.strTotalPrice = drOrder["strTotalPrice"].ToString();
				order.totalPrice = Convert.ToDouble(drOrder["totalPrice"]);
				order.discount = Convert.ToInt32(drOrder["discount"]);
				order.prePay = Convert.ToInt32(drOrder["prePay"]);
				order.remain = Convert.ToInt32(drOrder["remain"]);
				order.deliveryDate = drOrder["deliveryDate"] != DBNull.Value ? Convert.ToDateTime(drOrder["deliveryDate"]).ToString("dd/MM/yyyy") : "";
				order.createdDate = drOrder["createdDate"] != DBNull.Value ? Convert.ToDateTime(drOrder["createdDate"]).ToString("dd/MM/yyyy") : "";
				order.createdBy = Convert.ToInt32(drOrder["createdBy"]);
				order.payMethodId = Convert.ToInt32(drOrder["payMethodId"]);
				order.code = drOrder["code"].ToString();
				order.isFinished = Convert.ToBoolean(drOrder["isFinished"]);
				order.orderMaterials = new List<OrderMaterial>();
				myCommand.CommandText = string.Format("SELECT * FROM [OrderMaterial] WHERE [orderId]={0}", order.id);
				DataTable dtOderMaterial = new DataTable();
				myAdapter.Fill(dtOderMaterial);
				foreach (DataRow drOderMaterial in dtOderMaterial.Rows)
				{
					OrderMaterial orderMaterial = new OrderMaterial();
					orderMaterial.id = Convert.ToInt32(drOderMaterial["id"]);
					orderMaterial.materialId = drOderMaterial["materialId"].ToString();
					orderMaterial.description = drOderMaterial["description"].ToString();
					orderMaterial.orderQuantity = Convert.ToInt32(drOderMaterial["orderQuantity"]);
					orderMaterial.finishedQuantity = Convert.ToInt32(drOderMaterial["finishedQuantity"]);
					orderMaterial.finishedDate = drOderMaterial["finishedDate"].ToString();
					myCommand.CommandText = string.Format("SELECT * FROM [OrderMaterialPhase] WHERE [orderMaterialId]={0}", orderMaterial.id);
					DataTable dtOderMaterialPhase = new DataTable();
					myAdapter.Fill(dtOderMaterialPhase);
					orderMaterial.orderMaterialPhases = new List<OrderMaterialPhase>();
					foreach (DataRow dr in dtOderMaterialPhase.Rows)
						orderMaterial.orderMaterialPhases.Add(new OrderMaterialPhase(Convert.ToInt32(dr[0]), Convert.ToInt32(dr[1]), Convert.ToDouble(dr[2]), Convert.ToInt32(dr[3])));
					order.orderMaterials.Add(orderMaterial);
				}
				results.Add(order);
			}
			myConnection.Close();
			return results[0];
		}
    }
}